package com.zfp.core.utils;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;


import com.zfp.sys.entity.SysUser;



public class ExcelPoi { 
	HSSFSheet sheet; 

	HSSFRow row; //行 

	HSSFCellStyle style; //格式 

	HSSFFont boldFont; //字体 
	
	
	public void exportUser(List<SysUser> dataList,OutputStream out,String sheetName)throws Exception {
		try{

			List<String> headList = new ArrayList<String>();
			HSSFWorkbook wb = new HSSFWorkbook();
			sheet = wb.createSheet(sheetName); 
			sheet.setDefaultColumnWidth((short) 15);
			headList.add("登陆名");
			headList.add("姓名");
			headList.add("性别");
			headList.add("年龄");
			headList.add("电话");
			headList.add("备注");
			int headFlag = 1; 
			row = sheet.createRow(0); 
			style = this.createTitleStyle("title",wb);
			this.createCell(row, 0, style, "用户表");
			if (headList != null && headList.size() > 0 ) { 
				
				row = sheet.createRow(1);
				style = this.createTitleStyle("标题行新",wb);

				for (int i = 0; i < headList.size(); i++) { 

					this.createCell(row, i, style, headList.get(i)); 

				} 

				headFlag = 2; 

			}  
			if (dataList != null && dataList.size() > 0){ 
				SysUser user = null;

				style = this.createTitleStyle("正文",wb); 

				for(int i=0;i<dataList.size();i++){ 

					row = sheet.createRow(i+headFlag);// 建立新行 

					user = dataList.get(i); 
					this.createCell(row, 0, style, user.getLoginName());
					this.createCell(row, 1, style, user.getUserName());
					this.createCell(row, 2, style, user.getSex());
//					if(bb.getAmpm() == 0){
//						this.createCell(row, 3, style, "全天");
//					}else if(bb.getAmpm() == 1){
//						this.createCell(row, 3, style, "16点");
//					}else if(bb.getAmpm() == 2){
//						this.createCell(row, 3, style, "20点");
//					}else{
					this.createCell(row, 3, style, user.getAge());
//					}
					
					this.createCell(row,4, style, user.getPhone());
					this.createCell(row, 5, style, user.getRemark());
				
				} 
				
			} 

			wb.write(out);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			out.close();
		}
		
	}
	public void exportExcel(String jsonstr,OutputStream out,HSSFWorkbook wb,HSSFSheet sheet){
		try{
			if(null != jsonstr&&!jsonstr.equals("")){
			int statrt = 28;
			JSONObject jsonObj = JSONObject.fromObject(jsonstr);
			JSONArray ja = jsonObj.getJSONArray("result");
			int count = jsonObj.getInt("count");
			
			if(null != jsonObj.get("title")){
				mergeCells(sheet,statrt,0,statrt,1);
				row = sheet.createRow(statrt); 
				style = this.createTitleStyle("title",wb); 
				this.createCell(row, 0, style, jsonObj.getString("title")); 
				statrt = 29;
				
			}
			
			
			if(ja.size() > 0){
//			sheet.setDefaultColumnWidth((short) 15);
			row = sheet.createRow(statrt); 
			style = this.createTitleStyle("标题行新",wb); 
			JSONObject item = ja.getJSONObject(0);
			for (int i = 0; i < count; i++) { 
				this.createCell(row, i, style, item.get("obj"+i)); 
			} 

			if (ja != null && ja.size() > 1){ 
				style = this.createTitleStyle("正文",wb); 

				for(int i=1;i<ja.size();i++){

					row = sheet.createRow(i+statrt);// 建立新行
					item = ja.getJSONObject(i);
					for(int k = 0;k < count;k++){
					this.createCell(row, k, style,item.get("obj"+k));
					}
				} 
			} 
			}
			}
			wb.write(out);
		}catch(Exception e){
			e.printStackTrace();
		}
		
		
	}


	/** 

	 * 合并单元格 

	 * @param firstRow 开始行

	 * @param lastRow   开始列 

	 * @param firstCol 结束行

	 * @param lastCol 

	 */ 

	public void mergeCells(HSSFSheet sheet,int firstRow,int lastRow,int firstCol, int lastCol){ 

		sheet.addMergedRegion(new Region(firstRow,(short)lastRow, firstCol,(short)lastCol)); 

	} 

	/** 

	 * 创建Excel单元格 

	 * @param row 

	 * @param column 

	 * @param style 

	 * @param obj 

	 */ 

	@SuppressWarnings("deprecation")
	private void createCell(HSSFRow row, int column, HSSFCellStyle style, Object obj) { 

		HSSFCell cell = row.createCell((short) column); 

		cell.setCellType(HSSFCell.ENCODING_UTF_16); //防止出现中文乱码 

		if (style != null) { 

			cell.setCellStyle(style); 

		} 

		//判断传入值的类型 

		if (obj instanceof Integer || obj instanceof Double ) { 

			cell.setCellValue(Double.parseDouble(obj.toString( ))); 

		} else if (obj instanceof Float) { 

			cell.setCellValue(Float.parseFloat(obj.toString()) ); 

		} else if (obj == null) { 

			cell.setCellValue(""); 

		} else if (obj instanceof String){ 

			cell.setCellValue(obj.toString()); 

		} 

	} 

	/** 

	 * 获得样式 

	 * @param wb 

	 * @return 

	 */ 

	private HSSFCellStyle createTitleStyle(String flag,HSSFWorkbook wb) { 

		boldFont = wb.createFont(); 

		style = wb.createCellStyle(); 

		if ("标题".equals(flag)) { 

			boldFont.setFontHeight((short) 200); 
			boldFont.setBoldweight((short) 400);
			boldFont.setFontName("宋体"); 

			boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);



			style.setFont(boldFont); 

			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

			style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);   
			style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); 

			//			style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框   
			style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框   
			style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框   
			//	        style.setBorderTop(HSSFCellStyle.BORDER_THIN);

			//			style.setFillBackgroundColor(HSSFColor.LIGHT_YELLOW.index);
		} else if ("日期".equals(flag)) { 

			boldFont.setFontHeight((short) 200); 
			boldFont.setBoldweight((short) 400);

			boldFont.setFontName("宋体"); 
			boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
			style.setFont(boldFont); 

			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

			style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框   
			style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框   
			style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框   
			style.setBorderTop(HSSFCellStyle.BORDER_THIN);


			style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);   
			style.setFillForegroundColor(HSSFColor.SKY_BLUE.index); 
		} else if ("五".equals(flag)) { 

			boldFont.setFontHeight((short) 200); 
			boldFont.setBoldweight((short) 400);

			boldFont.setFontName("宋体"); 
			boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
			style.setFont(boldFont); 

			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

			style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框   
			style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框   
			style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框   
			style.setBorderTop(HSSFCellStyle.BORDER_THIN);


			style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);   
			style.setFillForegroundColor(HSSFColor.TEAL.index); 

		} else if ("十".equals(flag)) { 

			boldFont.setFontHeight((short) 200); 
			boldFont.setBoldweight((short) 400);

			boldFont.setFontName("宋体"); 
			boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
			style.setFont(boldFont); 

			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

			style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框   
			style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框   
			style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框   
			style.setBorderTop(HSSFCellStyle.BORDER_THIN);


			style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);   
			style.setFillForegroundColor(HSSFColor.ROYAL_BLUE.index);
			
		} else if ("十五".equals(flag)) { 

			boldFont.setFontHeight((short) 200); 
			boldFont.setBoldweight((short) 400);

			boldFont.setFontName("宋体"); 
			boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
			style.setFont(boldFont); 

			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

			style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框   
			style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框   
			style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框   
			style.setBorderTop(HSSFCellStyle.BORDER_THIN);


			style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);   
			style.setFillForegroundColor(HSSFColor.BRIGHT_GREEN.index);	
		} else if ("二十".equals(flag)) { 

			boldFont.setFontHeight((short) 200); 
			boldFont.setBoldweight((short) 400);

			boldFont.setFontName("宋体"); 
			boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
			style.setFont(boldFont); 

			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

			style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框   
			style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框   
			style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框   
			style.setBorderTop(HSSFCellStyle.BORDER_THIN);


			style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);   
			style.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);	
			
		} else if ("三十".equals(flag)) { 

			boldFont.setFontHeight((short) 200); 
			boldFont.setBoldweight((short) 400);

			boldFont.setFontName("宋体"); 
			boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
			style.setFont(boldFont); 

			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

			style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框   
			style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框   
			style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框   
			style.setBorderTop(HSSFCellStyle.BORDER_THIN);


			style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);   
			style.setFillForegroundColor(HSSFColor.RED.index);	
		} else if ("标题行".equals(flag)) { 

			boldFont.setFontHeight((short) 200); 
			boldFont.setBoldweight((short) 400);

			boldFont.setFontName("宋体"); 
			boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
			style.setFont(boldFont); 

			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

			style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框   
			style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框   
			style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框   
			style.setBorderTop(HSSFCellStyle.BORDER_THIN);


			style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);   
			style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); 


		} else if ("标题行新".equals(flag)) { 

			boldFont.setFontHeight((short) 200); 
			boldFont.setBoldweight((short) 400);

			boldFont.setFontName("宋体"); 
			boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
			style.setFont(boldFont); 

			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

			style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框   
			style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框   
			style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框   
			style.setBorderTop(HSSFCellStyle.BORDER_THIN);


			style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);   
			style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); 	
		} else if ("title".equals(flag)) { 

			boldFont.setFontHeight((short) 200); 
			boldFont.setBoldweight((short) 400);
			boldFont.setFontName("宋体"); 
			boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
			style.setFont(boldFont); 

			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//wo jia
			
//			style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框   
//			style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框   
//			style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框   
//			style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		} else if ("正文".equals(flag)) { 

			boldFont.setFontHeight((short) 200); 
			boldFont.setBoldweight((short) 200);
			boldFont.setFontName("宋体"); 

			style.setFont(boldFont); 

			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//wo jia
			
			style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框   
			style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框   
			style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框   
			style.setBorderTop(HSSFCellStyle.BORDER_THIN);

		} else if("正式".equals(flag)) {
			boldFont.setFontHeight((short) 250); 
			boldFont.setBoldweight((short) 200);
			boldFont.setFontName("Times New Roman"); 

			style.setFont(boldFont); 

			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);


			style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);   
			style.setFillForegroundColor(HSSFColor.RED.index);

		}

		return style; 

	} 

	/** 

	 * 测试得到datalist 

	 * @param dataList 

	 * @return 

	 */ 

	@SuppressWarnings("unchecked")
	public List adddataList(List dataList) { 

		for (int i = 0; i < 10; i++) { 

			Object[] objects = new Object[10] ; 

			for (int j = 0; j <10 ; j++) { 

				// objects[j] = String.valueOf(10-j); 

				objects[j] = 10-j; 

			} 

			dataList.add(objects); 

		} 

		return dataList; 

	} 
	/** 

	 * @param args 

	 */ 

	public static void main(String[] args) throws Exception{ 

				ExcelPoi excelPoi = new ExcelPoi(); 

				List dataList = new ArrayList(); 
				List mapList = new ArrayList();
				Map map = new HashMap();
				mapList.add(map);
				dataList = excelPoi.adddataList(dataList); 

		FileOutputStream fileOut = new FileOutputStream("e:/workbook.xls"); 

		System.out.println("导出开始"); 

		//		excelPoi.exportExcel(null,dataList,"hello").write( fileOut); 

		fileOut.close(); 

		System.out.println("导出完成"); 

	} 
 
} 

